# -*- coding:utf-8 -*-
import os
import pymysql
import json

with open('shicai.json', 'rb') as f:
    json_data = f.read()
json_data = json.loads(json_data)

mysql_config = {
    'host': '127.0.0.1',  # ip地址(本机地址可以写 localhost 或 127.0.0.1)
    'port': 3306,  # 端口号
    'user': 'root',  # 用户名
    'password': 'root',  # 密码
    'database': 'xt',  # 数据库名
    'charset': 'utf8',  # 字符集
}

for item in json_data:
    if 'market_price' in item:
        print(item)
        connect = pymysql.connect(**mysql_config)
        cursor = connect.cursor()  # 创建游标
        cat = item['category']
        cat1 = item['cat_top_name']
        cat2 = item['cat_second_name']
        try:
            sql = 'select id from cat where name=' + '"' + cat1 + '"'
            cursor.execute(sql)
        except Exception as e:
            print(e, 'select cat id')
            cursor.close()
            connect.close()
        else:
            cat1_tuple = cursor.fetchone()  # 返回符合查询条件的第一个数据,返回元祖

            if cat1_tuple:
                cat1_id = cat1_tuple[0]

            # category查询到对应的catid
            if not cat1_tuple:
                try:
                    # 插入语句
                    sql = 'insert into cat (name,source,level,parent) values (' + '"' + cat1 + '"' + ',' + '2'+','+'1'+','+'0' + ')'
                    # 执行插入语句
                    result = cursor.execute(sql)
                except Exception as e:
                    print(e, 'insert cat1')
                    cursor.close()
                    connect.close()
                else:
                    cat1_id = cursor.lastrowid

            node = str(cat1_id)+','
            try:
                # 更新语句
                sql = 'update cat set node=' + '"' + node + '"' + ' where id=' + str(cat1_id)
                # 执行插入语句
                result = cursor.execute(sql)
            except Exception as e:
                print('update cat1')
            else:
                try:
                    sql = 'select id from cat where name=' + '"' + cat2 + '"'
                    cursor.execute(sql)
                except Exception as e:
                    print(e, 'select cat2 id')
                    cursor.close()
                    connect.close()
                else:
                    cat2_tuple = cursor.fetchone()  # 返回符合查询条件的第一个数据,返回元祖

                    if cat2_tuple:
                        cat2_id = cat2_tuple[0]
                    if not cat2_tuple:
                        try:
                            # 插入语句
                            parent_id = cat1_id
                            sql = 'insert into cat (name,source,level,parent) values (' + '"' + cat2 + '"' + ',' + '2' +','+ '2' +','+ str(parent_id) + ')'
                            # 执行插入语句
                            result = cursor.execute(sql)
                        except Exception as e:
                            print(e, 'insert cat2')
                            cursor.close()
                            connect.close()
                        else:
                            cat2_id = cursor.lastrowid

                    node = node + str(cat2_id) + ','
                    try:
                        # 更新语句
                        sql = 'update cat set node=' + '"' + node + '"' + ' where id=' + str(cat2_id)
                        # 执行更新语句
                        result = cursor.execute(sql)
                    except Exception as e:
                        print('update cat2')
                    else:
                        try:
                            sql = 'select id from cat where name=' + '"' + cat + '"'
                            cursor.execute(sql)
                        except Exception as e:
                            print(e, 'select cat id')
                            cursor.close()
                            connect.close()
                        else:
                            cat_tuple = cursor.fetchone()  # 返回符合查询条件的第一个数据,返回元祖
                            if cat_tuple:
                                cat_id = cat_tuple[0]
                            if not cat_tuple:
                                try:
                                    # 插入语句
                                    parent_id = cat2_id
                                    sql = 'insert into cat (name,source,level,parent) values (' + '"' + cat + '"' + ',' + '2' +','+ '3'+',' + str(
                                        parent_id) + ')'
                                    # 执行插入语句
                                    result = cursor.execute(sql)
                                except Exception as e:
                                    print(e, 'insert cat3',cat)
                                    cursor.close()
                                    connect.close()
                                else:
                                    cat_id = cursor.lastrowid
                                    cat = item['category']
                                    try:
                                        sql = 'select id from cat where name=' + '"' + cat + '"'
                                        cursor.execute(sql)
                                    except Exception as e:
                                        print(e, 'select cat id')
                                        cursor.close()
                                        connect.close()
                                    else:
                                        cat_tuple = cursor.fetchone()
                                        cat_id = cat_tuple[0]
                                        node = node + str(cat_id)
                                        try:
                                            # 更新语句
                                            sql = 'update cat set node=' + '"' + node + '"' + ' where id=' + str(cat_id)
                                            # 执行插入语句
                                            result = cursor.execute(sql)
                                        except Exception as e:
                                            print('update cat3')
                                        else:
                                            connect.commit()
                            #         cat_id = cursor.lastrowid
                            #         brand = item['brand_id']
                            #         # 先查询brand表是否有这个数据
                            #         try:
                            #             sql = 'select id from brand where name=' + '"' + brand + '"'
                            #             cursor.execute(sql)
                            #         except Exception as e:
                            #             print(e, 'select brand is exists')
                            #             cursor.close()
                            #             connect.close()
                            #         else:
                            #             brand_tuple = cursor.fetchone()  # 返回符合查询条件的第一个数据,返回元祖
                            #             # 没有查询到有这条数据则执行插入
                            #             if not brand_tuple:
                            #                 try:
                            #                     # 插入语句
                            #                     sql = 'insert into brand (name,cat_id) values (' + '"' + brand + '"' + ',' + str(
                            #                         cat_id) + ')'
                            #                     # 执行插入语句
                            #                     result = cursor.execute(sql)
                            #                 except Exception as e:
                            #                     print(e, 'insert brand')
                            #                     cursor.close()
                            #                     connect.close()
                            #                 else:
                            #                     brand_id = cursor.lastrowid
                            #             if brand_tuple:
                            #                 brand_id = brand_tuple[0]
                            #
                            #             model = item['model']
                            #             # 先查询model表是否有这个数据
                            #             try:
                            #                 sql = 'select id from model where model_name=' +'"' + model + '"'
                            #                 cursor.execute(sql)
                            #             except Exception as e:
                            #                 print(e, 'select model is exists')
                            #                 cursor.close()
                            #                 connect.close()
                            #             else:
                            #                 model_tuple = cursor.fetchone()  # 返回符合查询条件的第一个数据,返回元祖
                            #                 # 没有查询到有这条数据则执行插入
                            #                 if not model_tuple:
                            #                     try:
                            #                         # 插入语句
                            #                         sql = 'insert into model (model_name,cat_id,source,brand_id) values (' + '"' + model+ '"' + ',' + str(cat_id) +','+'2'+','+ str(brand_id)+')'
                            #                         # 执行插入语句
                            #                         result = cursor.execute(sql)
                            #                     except Exception as e:
                            #                         print(e, 'insert model',brand_id,model,cat_id)
                            #                         cursor.close()
                            #                         connect.close()
                            #                     else:
                            #                         model_id = cursor.lastrowid
                            #                 if model_tuple:
                            #                     model_id = model_tuple[0]
                            #
                            #                 goods_name = item['goods_name']
                            #                 # try:
                            #                 #     sql = 'select id from goods where model_id=' + '"' + str(model_id) + '"' + 'and'+'goods_name=' + '"'+goods_name+'"'
                            #                 #     cursor.execute(sql)
                            #                 # except Exception as e:
                            #                 #     print(e, 'select goods is exists',model_id)
                            #                 #     cursor.close()
                            #                 #     connect.close()
                            #                 # else:
                            #                 #     goods_tuple = cursor.fetchone()  # 返回符合查询条件的第一个数据,返回元祖
                            #                 url = item['url']
                            #                 if 'market_price' and 'price_tag' in item:
                            #                     market_price = str(item['market_price'])
                            #
                            #                     price_tag = str(item['price_tag'])
                            #
                            #                     original_img = item['original_img']
                            #                     image = item['image']
                            #                     attr_value = item['attr_value']
                            #                     goods_color = item['goods_color']
                            #                     goods_attr = item['goods_attr']
                            #                     # if not goods_tuple:
                            #                     try:
                            #                         # 插入语句
                            #                         sql = 'insert into goods (cat_id,model_id,goods_name,url,original_img,image,attr_value,price,brand_id,goods_color,goods_attr,market_price) values (' +str(cat_id)+','+ '"' + str(
                            #                             model_id) + '"' + ','+ '"' + goods_name + '"'+','+'"'+url+'"'+','+'"'+original_img+'"'+','+'"'+image+'"'+','+'"'+attr_value+'"'+','+'"'+price_tag+'"'+','+str(brand_id)+','+'"'+goods_color+'"'+','+'"'+goods_attr+'"'+','+'"'+market_price+'"'+')'
                            #                         # 执行插入语句
                            #                         result = cursor.execute(sql)
                            #                     except Exception as e:
                            #                         print(e, 'insert goods',attr_value,image)
                            #                         cursor.close()
                            #                         connect.close()
                            #                     else:
                            #                         connect.commit()

# # 关闭游标和连接对象，释放资源
# cursor.close()
# connect.close()
print('操作完成')
